package constant.milk.aptprice.database;

import java.util.ArrayList;

import constant.milk.aptprice.bean.AptData;
import constant.milk.aptprice.bean.MyHouseData;
import android.content.Context;
import android.database.Cursor;

public class DBSelectQuery
{
	private Context context;
	private DbAdapter db;
	
	public DBSelectQuery(Context context, DbAdapter db)
	{
		this.context = context;
		this.db = db;
	}
	
	public void setBeginTansaction() throws Exception
	{
		db.beginTansaction(context);
	}
	
	public void setSuccessful() throws Exception
	{
		db.setTransactionSuccessful();
	}
	
	public void setEndTansaction() throws Exception
	{
		db.endTransaction(context);
	}
	
	public ArrayList<String> getHouseTitleList()
	{
		ArrayList<String> list = new ArrayList<String>();
		
		String query = "";
		
		query = "SELECT title FROM " + TableName.APT_DATA + " GROUP BY title ORDER BY title ASC"; 
		
		Cursor c = db.select(context, query);
		
		int titleIdx = c.getColumnIndex("title");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			String data = c.getString(titleIdx);
			
			list.add(data);
			
			c.moveToNext();
		}
		
		return list;
	}
	
	public ArrayList<AptData> getHouseList(String title)
	{
		ArrayList<AptData> list = new ArrayList<AptData>();
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.APT_DATA + " WHERE title = '" + title + "'" + 
				" ORDER BY name ASC";
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int titleIdx = c.getColumnIndex("title");
		int nameIdx = c.getColumnIndex("name");
		int meibIdx = c.getColumnIndex("meib");
		int junseIdx = c.getColumnIndex("junse");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			AptData data = new AptData();
			
			data.setId(c.getInt(idIdx) + "");
			data.setTitle(c.getString(titleIdx));
			data.setName(c.getString(nameIdx));
			data.setMeib(c.getString(meibIdx));
			data.setJunse(c.getString(junseIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		return list;
	}
	
	public AptData getHouseId(String id)
	{
		AptData data = new AptData();
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.APT_DATA + " WHERE id = " + id + ""; 
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int titleIdx = c.getColumnIndex("title");
		int nameIdx = c.getColumnIndex("name");
		int meibIdx = c.getColumnIndex("meib");
		int junseIdx = c.getColumnIndex("junse");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			data.setId(c.getInt(idIdx) + "");
			data.setTitle(c.getString(titleIdx));
			data.setName(c.getString(nameIdx));
			data.setMeib(c.getString(meibIdx));
			data.setJunse(c.getString(junseIdx));
			
			c.moveToNext();
		}
		
		return data;
	}
	
	public AptData getHouseTitleName(String title, String name)
	{
		AptData data = new AptData();
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.APT_DATA + " WHERE title = '" + title + "'" +
				" and name = '" + name + "';"; 
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int titleIdx = c.getColumnIndex("title");
		int nameIdx = c.getColumnIndex("name");
		int meibIdx = c.getColumnIndex("meib");
		int junseIdx = c.getColumnIndex("junse");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			data.setId(c.getInt(idIdx) + "");
			data.setTitle(c.getString(titleIdx));
			data.setName(c.getString(nameIdx));
			data.setMeib(c.getString(meibIdx));
			data.setJunse(c.getString(junseIdx));
			
			c.moveToNext();
		}
		
		return data;
	}
	
	public MyHouseData getMyHouse()
	{
		MyHouseData data = null;
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.MY_HOUSE;
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int titleIdx = c.getColumnIndex("title");
		int nameIdx = c.getColumnIndex("name");
		int pungIdx = c.getColumnIndex("pung");
		int dateIdx = c.getColumnIndex("date");
		int flagIdx = c.getColumnIndex("flag");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			data = new MyHouseData();
			data.setId(c.getInt(idIdx) + "");
			data.setTitle(c.getString(titleIdx));
			data.setName(c.getString(nameIdx));
			data.setPung(c.getString(pungIdx));
			data.setDate(c.getInt(dateIdx) + "");
			data.setFlag(c.getInt(flagIdx) + "");
			
			c.moveToNext();
		}
		
		return data;
	}
	
	/**
	 * 해당 테이블에 데이터가 있는지 확인한다.
	 * @param tableName
	 * @return
	 */
	public int getDBTableDataCount(String tableName)
	{
		String query = "";
		
		query = "SELECT * FROM " + tableName;
		
		Cursor c = db.select(context, query);
		
		int dbCount = 0;
		
		dbCount = c.getCount();
		
		c.close();
		
		return dbCount;
	}
}
